<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
	<module name="project_apply">

		<statement name="find_project_apply_by_project_apply_id" resultType="table">
			<![CDATA[
			select
			a.project_apply_id as projectApplyId,
			a.project_id as projectId,
			p.`name` as projectName,
			ifnull(b2.`key`,'') as stage,
			ifnull(b.`key`,'') as state,
			a.reason,
			a.reason_detail as reasonDetail,
			a.transfer_style as transferStyle,
			a.transfer_all_money as transferAllMoney,
			a.intellectual_property_all_money as intellectualPropertyAllMoney,
			a.transfer_count as transferCount,
			a.transfer_benifit as transferBenifit,
			a.transfer_tax as transferTax,
			a.transfer_exportincome as transferExportIncome,
			a.transfer_savedmoney as transferSavedMoney,
			a.transfer_budget_invest as transferBudgetInvest,
			a.transfer_budget_benifit as transferBudgetBenifit,
			b.`value` as stateValue,
			b1.`value` as transferStyleValue,
			b2.`value` as stageValue,
			case a.stage
			when '1' then null
			when '2' then (select b3.`value` from sys_code b3 where  a.reason=b3.`key` and b3.`type`= 25)
			when '3' then (select b3.`value` from sys_code b3 where a.reason=b3.`key` and b3.`type`= 25)
			when '4' then null
			when '5' then null
			end as reasonValue,
			case a.stage
			when '1' then null
			when '2' then
			(case a.reason
			when '1' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 17)
			when '2' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 14)
			when '3' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 13)
			when '4' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 16)
			when '5' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 15)
			when '6' then null
			end)	
			when '3' then
			(case a.reason
			when '1' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 17)
			when '2' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 14)
			when '3' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 13)
			when '4' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 16)
			when '5' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 15)
			when '6' then null
			end)		
			when '4' then null
			when '5' then null			
			end as reasonDetailValue,
			a.is_saved as isSaved
			from project_apply as a
			join project as p on a.project_id = p.project_id 
			left join sys_code b on a.state=b.`key` and b.`type`= 20
			left join sys_code b1 on a.transfer_style=b1.`key` and b1.`type`= 24
			left join sys_code b2 on a.stage=b2.`key` and b2.`type`= 21
			where project_apply_id=#projectapply_id#	
			order by projectName
			]]>	
			<parameter name="projectapply_id" type="string" />			
		</statement>

		<statement name="find_project_apply_by_project_id" resultType="table">
			<![CDATA[
			select
			a.project_apply_id as projectApplyId,
			a.project_id as projectId,
			p.`name` as projectName,
			ifnull(b2.`key`,'') as stage,
			ifnull(b.`key`,'') as state,
			a.reason,
			a.reason_detail as reasonDetail,
			a.transfer_style as transferStyle,
			a.transfer_all_money as transferAllMoney,
			a.intellectual_property_all_money as intellectualPropertyAllMoney,
			a.transfer_count as transferCount,
			a.transfer_benifit as transferBenifit,
			a.transfer_tax as transferTax,
			a.transfer_exportincome as transferExportIncome,
			a.transfer_savedmoney as transferSavedMoney,
			a.transfer_budget_invest as transferBudgetInvest,
			a.transfer_budget_benifit as transferBudgetBenifit,
			b.`value` as stateValue,
			b1.`value` as transferStyleValue,
			b2.`value` as stageValue,
			case a.stage
			when '1' then null
			when '2' then (select b3.`value` from sys_code b3 where  a.reason=b3.`key` and b3.`type`= 25)
			when '3' then (select b3.`value` from sys_code b3 where a.reason=b3.`key` and b3.`type`= 25)
			when '4' then null
			when '5' then null
			end as reasonValue,
			case a.stage
			when '1' then null
			when '2' then
			(case a.reason
			when '1' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 17)
			when '2' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 14)
			when '3' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 13)
			when '4' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 16)
			when '5' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 15)
			when '6' then null
			end)
			when '3' then
			(case a.reason
			when '1' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 17)
			when '2' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 14)
			when '3' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 13)
			when '4' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 16)
			when '5' then (select b4.`value` from  sys_code b4 where a.reason_detail=b4.`key` and b4.`type`= 15)
			when '6' then null
			end)		
			when '4' then null
			when '5' then null			
			end as reasonDetailValue
			from project_apply as a
			join project as p on a.project_id = p.project_id 
			left join sys_code b on a.state=b.`key` and b.`type`= 20
			left join sys_code b1 on a.transfer_style=b1.`key` and b1.`type`= 24
			left join sys_code b2 on a.stage=b2.`key` and b2.`type`= 21
			where p.project_id=#project_id#
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="state">
					<![CDATA[
					a.`state` = #state#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			order by projectName
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="state" type="string" />
		</statement>


		<statement name="exsits_by_id" resultType="bool">
			<![CDATA[
			select exists(select project_id	from project_apply 	where project_id=#project_id#);
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_apply" allowPaging="true" resultType="table">
			<![CDATA[
			select
			a.project_id as projectId,
			b.`name` as projectName,
			a.project_apply_id as projectApplyId,
			b.`type`,
			case b.`type`
			when '1' then '应用技术类'
			when '2' then '软科学研究类'
			when '3' then '基础理论类'
			end as typeValue,
			ifnull(sc.`key`,'') as `state`,
			sc.`value` as stateValue,
			a.transfer_count as transferCount,
			a.transfer_all_money as transferAllMoney
			from project_apply as a
			join project as b on a.project_id=b.project_id and b.status=4
			join sys_code as sc on a.`state` =sc.`key` and sc.type=20			
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					b.`name` like concat('%', #project_name# , '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="primarykey">
					<![CDATA[
					(b.primary_key1 like concat('%',#primarykey#,'%')
					or b.primary_key2 like concat('%',#primarykey#,'%')
					or b.primary_key3 like concat('%',#primarykey#,'%'))
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="stage">
					<![CDATA[
					a.stage=#stage#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="type">
					<![CDATA[
					b.`type`=#type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="level">
					<![CDATA[
					b.`level`=#level#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="state">
					<![CDATA[
					a.`state` in ($state$)
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					b.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			order by b.`name`
			]]>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="primarykey" type="string" matchType="contains"/>
			<parameter name="stage" type="string" />
			<parameter name="type" type="string" />
			<parameter name="level" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="state" type="string" />
		</statement>

		<statement name="find_project_apply_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_apply as a
			join project as b on a.project_id=b.project_id and b.status=4
			join sys_code as sc on a.`state` =sc.`key` and sc.type=20
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					b.`name` like concat('%', #project_name# , '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="primarykey">
					<![CDATA[
					(b.primary_key1 like concat('%',#primarykey#,'%')
					or b.primary_key2 like concat('%',#primarykey#,'%')
					or b.primary_key3 like concat('%',#primarykey#,'%'))
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="stage">
					<![CDATA[
					a.stage=#stage#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="type">
					<![CDATA[
					b.`type`=#type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="level">
					<![CDATA[
					b.`level`=#level#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="state">
					<![CDATA[
					a.`state` in($state$)
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					b.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="primarykey" type="string" matchType="contains"/>
			<parameter name="stage" type="string" />
			<parameter name="type" type="string" />
			<parameter name="level" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="state" type="string" />
		</statement>

		<statement name="delete_project_apply">
			<![CDATA[
			delete from project_apply_item where project_apply_id = #projectapply_id#;
			delete from project_apply_check where project_apply_id=#projectapply_id#;
			delete from project_apply where project_apply_id=#projectapply_id#;
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="change_status">
			<![CDATA[
			update project_apply set state=#new_status#
			where project_apply_id=#projectapply_id#
			]]>
			<parameter name="projectapply_id" type="string" />
			<parameter name="new_status" type="int" />
		</statement>

		<statement name="find_project_apply_items_by_project_apply_id" allowPaging="true" resultType="table">
			<![CDATA[
			select
			seq,
			pai.project_apply_id as projectApplyId,
			transfer_company as transferCompany,
			transfer_date as transferDate,
			transfer_money as transferMoney,
			intellectual_property_money as intellectualPropertyMoney,
			pai.entity_id as entityId,
			pe.`name` as entityName
			from project_apply_item as pai
			join project_apply as pa on pai.project_apply_id = pa.project_apply_id 
			join project_entities as pe on pa.project_id = pe.project_id 
			and pai.entity_id = pe.entity_id
			where pai.project_apply_id=#projectapply_id#
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="find_project_apply_items_by_project_apply_id_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_apply_item as pai
			join project_apply as pa on pai.project_apply_id = pa.project_apply_id 
			join project_entities as pe on pa.project_id = pe.project_id 
			and pai.entity_id = pe.entity_id
			where pai.project_apply_id=#projectapply_id#
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="find_project_apply_items_by_project_id" allowPaging="true" resultType="table">
			<![CDATA[
			select
			seq,
			pai.project_apply_id as projectApplyId,
			transfer_company as transferCompany,
			transfer_date as transferDate,
			transfer_money as transferMoney,
			intellectual_property_money as intellectualPropertyMoney,
			pai.entity_id as entityId,
			pe.`name` as entityName
			from project_apply_item as pai
			join project_apply as pa on pai.project_apply_id = pa.project_apply_id 
			join project_entities as pe on pa.project_id = pe.project_id 
			and pai.entity_id = pe.entity_id
			where pe.project_id=#project_id#			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="state">
					<![CDATA[
					pa.`state` = #state#
					]]>
				</isNotEmpty>
			</dynamic>		
			<parameter name="project_id" type="string" />
			<parameter name="state" type="string" />
		</statement>

		<statement name="find_project_apply_items_by_project_id_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_apply_item as pai
			join project_apply as pa on pai.project_apply_id = pa.project_apply_id 
			join project_entities as pe on pa.project_id = pe.project_id 
			and pai.entity_id = pe.entity_id
			where pe.project_id=#project_id#
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="state">
					<![CDATA[
					pa.`state` = #state#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="project_id" type="string" />
			<parameter name="state" type="string" />
		</statement>



		<statement name="find_project_apply_item_sum" resultType="table">
			<![CDATA[
			select
			count(*) as recordCount,
			IFNULL(sum(transfer_money),0) as allmoney1,
			IFNULL(sum(intellectual_property_money),0) as allmoney2
			from
			project_apply_item
			where project_apply_id=#projectapply_id#
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="create_project_apply">
			<![CDATA[
			insert into project_apply
			(
			project_apply_id,
			project_id,
			state,
			stage,
			reason,
			reason_detail,
			transfer_style,
			transfer_all_money,
			intellectual_property_all_money,
			transfer_count,
			transfer_benifit,
			transfer_tax,
			transfer_exportincome,
			transfer_savedmoney,
			transfer_budget_invest,
			transfer_budget_benifit,
			is_saved
			)
			values
			(
			#projectapply_id#,
			#project_id#,
			#state#,
			#stage#,
			#reason#,
			#reason_detail#,
			#transfer_style#,
			#transfer_all_money#,
			#intellectualproperty_all_money#,
			#transfer_count#,
			#transfer_benifit#,
			#transfer_tax#,
			#transfer_exportincome#,
			#transfer_savedmoney#,
			#transfer_budget_invest#,
			#transfer_budget_benifit#,
			#is_saved#
			)
			]]>
			<parameter name="projectapply_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="state" type="int" />
			<parameter name="stage" type="string" />
			<parameter name="reason" type="string" />
			<parameter name="reason_detail" type="string" />
			<parameter name="transfer_style" type="string" />
			<parameter name="transfer_all_money" type="money" />
			<parameter name="intellectualproperty_all_money" type="money" />
			<parameter name="transfer_count" type="int" />
			<parameter name="transfer_benifit" type="money" />
			<parameter name="transfer_tax" type="money" />
			<parameter name="transfer_exportincome" type="money" />
			<parameter name="transfer_savedmoney" type="money" />
			<parameter name="transfer_budget_invest" type="money" />
			<parameter name="transfer_budget_benifit" type="money" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="update_project_apply_basic_info">
			<![CDATA[
			update project_apply
			set stage=#stage#,
			reason=#reason#,
			reason_detail=#reason_detail#,
			transfer_style=#transfer_style#,
			transfer_benifit=#transfer_benifit#,
			transfer_tax=#transfer_tax#,
			transfer_exportincome=#transfer_exportincome#,
			transfer_savedmoney=#transfer_savedmoney#,
			transfer_budget_invest=#transfer_budget_invest#,
			transfer_budget_benifit=#transfer_budget_benifit#,
			is_saved=#is_saved#
			where project_apply_id=#projectapply_id#
			]]>
			<parameter name="projectapply_id" type="string" />
			<parameter name="stage" type="string" />
			<parameter name="reason" type="string" />
			<parameter name="reason_detail" type="string" />
			<parameter name="transfer_style" type="string" />
			<parameter name="transfer_benifit" type="money" />
			<parameter name="transfer_tax" type="money" />
			<parameter name="transfer_exportincome" type="money" />
			<parameter name="transfer_savedmoney" type="money" />
			<parameter name="transfer_budget_invest" type="money" />
			<parameter name="transfer_budget_benifit" type="money" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="update_project_apply_money_info">
			<![CDATA[
			update project_apply p  
			set p.transfer_all_money = (select ifnull(sum(ifnull(q.transfer_money, 0)),0) from project_apply_item q where q.project_apply_id=#projectapply_id#) 
			where p.project_apply_id=#projectapply_id#;
			update project_apply p  
			set p.intellectual_property_all_money = (select ifnull(sum(ifnull(q.intellectual_property_money, 0)),0) from project_apply_item q where q.project_apply_id=#projectapply_id#) 
			where p.project_apply_id=#projectapply_id#;
			update project_apply p  
			set p.transfer_count = (select count(*) from project_apply_item q where q.project_apply_id=#projectapply_id#) 
			where p.project_apply_id=#projectapply_id#;
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="find_project_apply_item_by_id" resultType="table">
			<![CDATA[
			select
			seq,
			project_apply_id as projectApplyId,
			transfer_company as transferCompany,
			transfer_date as transferDate,
			transfer_money as transferMoney,
			intellectual_property_money as intellectualPropertyMoney,
			entity_id as entityId
			from project_apply_item
			where seq=#seq#
			]]>
			<parameter name="seq" type="string" />
		</statement>

		<statement name="delete_project_apply_item">
			<![CDATA[
			delete from project_apply_item where seq = #seq#;
			]]>
			<parameter name="seq" type="string" />
		</statement>

		<statement name="create_project_apply_item">
			<![CDATA[
			insert into project_apply_item
			(
			seq,
			project_apply_id,
			transfer_company,
			transfer_date,
			transfer_money,
			intellectual_property_money,
			entity_id
			)
			values
			(
			#seq#,
			#projectapply_id#,
			#transfer_company#,
			#transfer_date#,
			#transfer_money#,
			#intellectualproperty_money#,
			#entity_id#
			)
			]]>
			<parameter name="seq" type="string" />
			<parameter name="projectapply_id" type="string" />
			<parameter name="transfer_company" type="string" />
			<parameter name="transfer_date" type="datetime" />
			<parameter name="transfer_money" type="money" />
			<parameter name="intellectualproperty_money" type="money" />
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="find_project_apply_check" allowPaging="true" resultType="table">
			<![CDATA[
			select
			p.`name` as projectName,
			pac.seq as projectApplyCheckId,
			pac.project_apply_id as projectApplyId ,
			pac.date,
			pac.`option`,
			pac.`comment`,
			pac.user_id as userId,
			su.`real_name` as userName
			from project_apply_check as pac 
			join sec_user as su on pac.user_id = su.user_id
			join project_apply as pa on pac.project_apply_id = pa.project_apply_id 
			join project as p on pa.project_id = p.project_id
			where pac.project_apply_id = #projectapply_id#
			order by pac.date desc
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="find_project_apply_check_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_apply_check as pac 
			join sec_user as su on pac.user_id = su.user_id
			join project_apply as pa on pac.project_apply_id = pa.project_apply_id 
			join project as p on pa.project_id = p.project_id
			where pac.project_apply_id = #projectapply_id#	
			]]>
			<parameter name="projectapply_id" type="string" />
		</statement>

		<statement name="find_project_apply_check_by_id" resultType="table">
			<![CDATA[
			select
			p.`name` as projectName,
			pac.seq as projectApplyCheckId,
			pac.project_apply_id as projectApplyId,
			pac.date,
			pac.`option`,
			pac.`comment`,			
			pac.user_id as userId
			from project_apply_check as pac
			join project_apply as pa on pac.project_apply_id = pa.project_apply_id 
			join project as p on pa.project_id = p.project_id
			where pac.seq = #project_apply_check_id# 
			]]>
			<parameter name="project_apply_check_id" type="string" />

		</statement>

		<!--<statement name="deleteProjectApplyCheck">
            <![CDATA[
    delete from project_apply_check where project_apply_id = #projectapply_id#
				]]>
            <parameter name="projectapply_id" type="string" />
        </statement>-->

		<statement name="create_project_apply_check">
			<![CDATA[
			insert into project_apply_check
			(
			seq,
			project_apply_id,
			date,
			`option`,
			`comment`,
			user_id)
			values
			(
			#seq#,
			#projectapply_id#,
			#date#,
			#option#,
			#comment#,
			#user_id#)
			]]>
			<parameter name="seq" type="string" />
			<parameter name="projectapply_id" type="string" />
			<parameter name="date" type="datetime" />
			<parameter name="option" type="string" />
			<parameter name="comment" type="string" />
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="find_project_apply_sum" resultType="table">
			<![CDATA[			
			select
			YEAR(transfer_date) as year,
			name,
			dep_id as depId,
			departmentName,
			stageName,
			count(*) as recordCount,
			sum(transfer_money) as transferYearlyMoney,
			sum(intellectualproperty_money) as intellectualPropertyYearlyMoney
			from
			projectApplySumTemp
			where transfer_date between #min_date# and #max_date#			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					dep_id = #dep_id#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="transfer_all_money">
					<![CDATA[
					transfer_money >= #transfer_all_money#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="intellectualproperty_all_money">
					<![CDATA[
					intellectualproperty_money >= #intellectualproperty_all_money#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by year(transfer_date),dep_id			
			]]>
			<parameter name="min_date" type="date" />
			<parameter name="max_date" type="date" />
			<parameter name="dep_id" type="string" />
			<parameter name="transfer_all_money" type="money" />
			<parameter name="intellectualproperty_all_money" type="money" />
		</statement>
	</module>
</root>
